package database;

import helpers.FinDate;

import java.io.ByteArrayInputStream;
import java.io.File;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import javax.sql.rowset.serial.SerialBlob;

import objects.Bin;
import objects.Transaction;

import managers.BinManager;



/** Database connection class & utilities **/

public class DBManager extends DBBase {
	private static PreparedStatement STATEMENT_INSERT_TRANSACTION;
	private static final String STR_STATEMENT_INSERT_TRANSACTION = "INSERT INTO transactions VALUES(?, ?, ?, ?);";

	private static PreparedStatement STATEMENT_INSERT_USER;
	private static final String STR_STATEMENT_INSERT_USER = "INSERT INTO users VALUES(?, ?)";
	
	private static final String URL = "jdbc:sqlite:db_dir/finances.db";
	private static final String DRIVER = "org.sqlite.JDBC";

	public static void initDBManager() {
		try {
			File dir = new File("./db_dir");
			dir.mkdirs();
			init(DBManager.DRIVER, DBManager.URL);

			if(conn != null) {
				System.out.println("Connected OK using " + DBManager.DRIVER + " to " + DBManager.URL);
				Statement stat = conn.createStatement();
				// Note: in SQLite INTEGER is a 1 to 8 byte signed integer, i.e. it can contain Java long's 
				stat.execute("CREATE TABLE IF NOT EXISTS transactions (accountNumber INTEGER, amount INTEGER, binNumber INTEGER, message STRING);");
				stat.execute("CREATE TABLE IF NOT EXISTS bins (binNumber INTEGER, name STRING, desiredAmount INTEGER, desiredDate INTEGER);");
				stat.execute("CREATE TABLE IF NOT EXISTS users (username STRING UNIQUE NOT NULL, hash BLOB);");

				STATEMENT_INSERT_TRANSACTION = conn.prepareStatement(STR_STATEMENT_INSERT_TRANSACTION);
				STATEMENT_INSERT_USER = conn.prepareStatement(STR_STATEMENT_INSERT_USER);
			}
			else {
				System.out.println("Connection failed");
			}
		} catch (Exception e) {
			// TODO Auto-generated catch block
			System.out.println("Connection failed with error!!");
			e.printStackTrace();
		}
	}


	public static void insetTransaction(Transaction transaction) {
		try {
			STATEMENT_INSERT_TRANSACTION.setInt(1, transaction.getAccountNumber());
			STATEMENT_INSERT_TRANSACTION.setLong(2, transaction.getAmount());
			STATEMENT_INSERT_TRANSACTION.setInt(3, transaction.getBinNumber());
			STATEMENT_INSERT_TRANSACTION.setString(4, transaction.getMessage());
			STATEMENT_INSERT_TRANSACTION.addBatch();
			STATEMENT_INSERT_TRANSACTION.executeBatch();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}


	public static void getBinByName(String binName)  {

		try {
			Statement stat= conn.createStatement();

			ResultSet rs =stat.executeQuery("SELECT * FROM bins WHERE name='"+binName+"';");

			if(rs.next()) {
				// TODO improve handling, we want to check if the bin in the DB even has a desireddate
				BinManager.addBin(new Bin(rs.getInt("binNumber"), rs.getString("name"), rs.getLong("desiredAmount"), new FinDate(rs.getInt("desiredDate"))));
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

	}

	public static boolean insertUser(String username, byte[] passwordhash)  {
		try {
			STATEMENT_INSERT_USER.setString(1, username);
			STATEMENT_INSERT_USER.setBytes(2,passwordhash);
			STATEMENT_INSERT_USER.addBatch();
			STATEMENT_INSERT_USER.executeBatch();
			
		} catch (SQLException e) {
			// if the error is that the user already exists, that's fine - we just return false then. If *not*
			// we want the error message
			if(!e.getMessage().equalsIgnoreCase("column username is not unique")) {
				e.printStackTrace();
			}
			return false;
		}
		return true;

	}
	
	public static byte[] getUserHash(String username)  {
		byte[] out = null;
		try {
			Statement stat;
			stat = conn.createStatement();

			ResultSet rs =stat.executeQuery("SELECT * FROM users WHERE username='"+username+"';");
			if(rs.next()) {
				out =  rs.getBytes("hash");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		return out;
	}
}
